package ua.races.dao;

import org.codehaus.jackson.map.ObjectMapper;
import org.codehaus.jackson.type.TypeReference;
import org.h2.jdbcx.JdbcConnectionPool;
import ua.races.relultObjects.FullBet;
import ua.races.relultObjects.FullRace;
import ua.races.vo.*;

import java.io.*;
import java.sql.*;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.List;

public class RacesDaoImpl implements UserDao, AdminDao, BookmakerDao, Dao {
    private JdbcConnectionPool cp;

    private String db;

    public RacesDaoImpl() {
        db = "jdbc:h2:database/NewBase";
    }

    public RacesDaoImpl(String db) {
        this.db = db;
    }

    public void start() throws SQLException, IOException {
        cp = createPool();
        cp.setMaxConnections(10);
    }

    public void stop() throws SQLException {
        cp.dispose();
        System.out.println("cp.getActiveConnections() = " + cp.getActiveConnections());
    }

    /**
     * do not create a connection each time, use a {@link javax.sql.DataSource} which pools connections
     *
     * @return connection from pool
     * @throws SQLException if something goes wrong
     */
    public Connection getConnection() throws SQLException {
        System.out.println("creating connection!");

        Connection connection = cp.getConnection();

        System.out.println("cp.getActiveConnections() = " + cp.getActiveConnections());

        return connection;
    }

    public List<Horse> findAllHorses() throws SQLException {
        final List<Horse> horses;

        Connection conn = null;
        Statement stat = null;

        try {
            conn = getConnection();
            stat = conn.createStatement();
            ResultSet resultSet = stat.executeQuery("select * from Horse");

            horses = new ArrayList<Horse>();
            while (resultSet.next()) {
                horses.add(new Horse(
                        resultSet.getLong("Horse_Id"),
                        resultSet.getString("Horse_name")
                ));
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return horses;
    }


    @Override
    public List<Race> findAllRacesForBet() throws SQLException {

        final List<Race> races;
        Race race = null;
        Connection conn = null;
        Statement stat = null;


        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            // todo wrong SQL
            ResultSet resultSet = stat.executeQuery("select race.race_id, race.start_time, race.stadium, race.race_date" +
                    " From race" +
                    " Inner Join horse_race" +
                    " on" +
                    " ((race.race_id=horse_race.race_id) " +
                    " and (horse_race.coef is not null)" +
                    " and (horse_race.result_place is null)" +
                    " and (race.race_date>=current_date))" +
                    " group by (race.race_id, race.start_time, race.stadium, race.race_date)");


            races = new ArrayList<Race>();
            while (resultSet.next()) {
                race = new Race();
                race.setRaceId(resultSet.getLong("RACE_ID"));
                race.setRaceDate(resultSet.getDate("Race_date"));
                race.setStadium(resultSet.getString("STADIUM"));
                race.setStartTime(resultSet.getTime("START_TIME"));


                races.add(race);
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return races;
    }

    public List<Race> findAllRacesWithHorse(Horse horse) throws SQLException {
        final List<Race> races;
        Race race;
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("SELECT race.race_id, race.race_date , race.stadium ,race.start_time " +
                    "FROM race, horse_race " +
                    "WHERE horse_race.horse_id = (?) and race.race_id= horse_race.race_id;");
            // todo prepared statement
            // todo FUTURE OR ALL???
            prepStat.setLong(1, horse.getHorseId());
            ResultSet resultSet = prepStat.executeQuery();

            races = new ArrayList<Race>();
            while (resultSet.next()) {

                race = new Race();
                race.setRaceId(resultSet.getLong("Race_id"));
                race.setRaceDate(resultSet.getDate("Race_date"));
                race.setStadium(resultSet.getString("Stadium"));
                race.setStartTime(resultSet.getTime("Start_time"));

                races.add(race);
            }

        } finally {
            silentClose(conn);
            silentClose(prepStat);
        }

        return races;
    }

    public int addNewClient(Client client) throws SQLException {

        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = getConnection();
            prepStat = conn.prepareStatement("INSERT INTO CLIENT (FIRST_NAME, LAST_NAME, PHONE_NUMBER, EMAIL, MONEY, PASSWORD) " +
                    "VALUES (?, ?, ?, ?, ?, ?)");

            prepStat.setString(1, client.getFirstName());
            prepStat.setString(2, client.getLastName());
            if (client.getPhoneNumber() == null) {
                prepStat.setNull(3, Types.VARCHAR);
            } else {
                prepStat.setString(3, client.getPhoneNumber());
            }
            prepStat.setString(4, client.getEmail());
            prepStat.setFloat(5, client.getMoney());
            prepStat.setLong(6, client.getPassword());

            return prepStat.executeUpdate();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }

    }

    /**
     * Adds client (using for synchronize)
     *
     * @param client to add
     * @return 1 if everything was alright
     * @throws SQLException if something goes wrong
     */
    private int addClient(Client client) throws SQLException {

        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = getConnection();
            prepStat = conn.prepareStatement("INSERT INTO CLIENT (FIRST_NAME, LAST_NAME, " +
                    "PHONE_NUMBER, EMAIL, MONEY, CLIENT_ID, PASSWORD) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?)");

            prepStat.setString(1, client.getFirstName());
            prepStat.setString(2, client.getLastName());
            if (client.getPhoneNumber() == null) {
                prepStat.setNull(3, Types.VARCHAR);
            } else {
                prepStat.setString(3, client.getPhoneNumber());
            }
            prepStat.setString(4, client.getEmail());
            prepStat.setFloat(5, client.getMoney());
            prepStat.setLong(6, client.getClientId());
            prepStat.setLong(7, client.getPassword());

            return prepStat.executeUpdate();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }

    }

    public int addNewBet(Bet bet) throws SQLException {

        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("INSERT INTO BET" +
                    " (CLIENT_ID, RACE_ID, BET_TYPE_ID, BET_DATE, FIRST_HORSE, SECOND_HORSE, THIRD_HORSE, BET_VALUE, RESULT) " +
                    "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            prepStat.setLong(1, bet.getClientId());
            prepStat.setLong(2, bet.getRaceId());
            prepStat.setLong(3, bet.getBetTypeId());
            prepStat.setDate(4, bet.getBetDate());
            prepStat.setLong(5, bet.getFirstHorse());
            if (bet.getSecondHorse() == null) {
                prepStat.setNull(6, Types.INTEGER);
            } else {
                prepStat.setLong(6, bet.getSecondHorse());
            }
            if (bet.getThirdHorse() == null) {
                prepStat.setNull(7, Types.INTEGER);
            } else {
                prepStat.setLong(7, bet.getThirdHorse());
            }
            prepStat.setDouble(8, bet.getBetValue());
            if (bet.getResult() == null) {
                prepStat.setNull(9, Types.VARCHAR);
            } else {
                prepStat.setString(9, bet.getResult());
            }

            return prepStat.executeUpdate();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
    }

    /**
     * Adds bet (using for synchronize)
     *
     * @param bet to add
     * @return 1 if everything was alright
     * @throws SQLException if something goes wrong
     */
    private int addBet(Bet bet) throws SQLException {

        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("INSERT INTO BET" +
                    " (CLIENT_ID, RACE_ID, BET_TYPE_ID, BET_DATE, " +
                    "FIRST_HORSE, SECOND_HORSE, THIRD_HORSE, BET_VALUE, BET_ID, RESULT) " +
                    "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            prepStat.setLong(1, bet.getClientId());
            prepStat.setLong(2, bet.getRaceId());
            prepStat.setLong(3, bet.getBetTypeId());
            prepStat.setDate(4, bet.getBetDate());
            prepStat.setLong(5, bet.getFirstHorse());
            if (bet.getSecondHorse() == null) {
                prepStat.setNull(6, Types.INTEGER);
            } else {
                prepStat.setLong(6, bet.getSecondHorse());
            }
            if (bet.getThirdHorse() == null) {
                prepStat.setNull(7, Types.INTEGER);
            } else {
                prepStat.setLong(7, bet.getThirdHorse());
            }
            prepStat.setDouble(8, bet.getBetValue());
            prepStat.setLong(9, bet.getBetId());
            if (bet.getResult() == null) {
                prepStat.setNull(10, Types.VARCHAR);
            } else {
                prepStat.setString(10, bet.getResult());
            }


            return prepStat.executeUpdate();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
    }

    public int addNewRace(Race race) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        int key = 0;
        try {
            conn = cp.getConnection();

            prepStat = conn.prepareStatement("INSERT INTO RACE" +
                    " (RACE_DATE, STADIUM, START_TIME) " +
                    "VALUES (?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            prepStat.setDate(1, race.getRaceDate());
            prepStat.setString(2, race.getStadium());
            prepStat.setTime(3, race.getStartTime());

            prepStat.executeUpdate();
            ResultSet rs = prepStat.getGeneratedKeys();
            if (rs.next()) {
                // Retrieve the auto generated key(s).
                key = rs.getInt(1);
            }
            return key;

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
    }

    /**
     * Add race inserts race with non-autogenerated id (uses for synchronization)
     *
     * @param race to add
     * @return 1 if everything was alright
     * @throws SQLException if something goes wrong
     */
    private int addRace(Race race) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();

            prepStat = conn.prepareStatement("INSERT INTO RACE" +
                    " (RACE_ID ,RACE_DATE, STADIUM, START_TIME) " +
                    "VALUES (?, ?, ?, ?)");

            prepStat.setLong(1, race.getRaceId());
            prepStat.setDate(2, race.getRaceDate());
            prepStat.setString(3, race.getStadium());
            prepStat.setTime(4, race.getStartTime());

            return prepStat.executeUpdate();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
    }

    public List<Race> findAllRacesForAdmin() throws SQLException {

        final List<Race> races;
        Race race = null;
        Connection conn = null;
        Statement stat = null;


        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            // todo SQL current time
            ResultSet resultSet = stat.executeQuery("select race.race_id, race.start_time, race.stadium, race.race_date" +
                    " from race" +
                    " Inner Join horse_race on (" +
                    " (race.race_id=horse_race.race_id) and" +
                    " (horse_race.coef is not null) and" +
                    " (horse_race.result_place is null) and" +
                    " (race.Race_Date<=current_date) )" +
                    " group by (race.race_id,race.start_time,race.stadium,race.race_date)");

            races = new ArrayList<Race>();
            while (resultSet.next()) {
                race = new Race();
                race.setRaceId(resultSet.getLong("RACE_ID"));
                race.setRaceDate(resultSet.getDate("RACE_DATE"));
                race.setStadium(resultSet.getString("STADIUM"));
                race.setStartTime(resultSet.getTime("START_TIME"));


                races.add(race);
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return races;
    }

    public List<BetType> findAllBetTypes() throws SQLException {
        final List<BetType> betTypes;
        BetType betType = null;
        Connection conn = null;
        Statement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            ResultSet resultSet = stat.executeQuery("SELECT * FROM BET_TYPE ");

            betTypes = new ArrayList<BetType>();

            while (resultSet.next()) {
                betType = new BetType();
                betType.setBetTypeId(resultSet.getLong("BET_TYPE_ID"));
                betType.setBetType(resultSet.getString("BET_TYPE"));

                betTypes.add(betType);
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return betTypes;
    }

    public List<Race> findAllRacesForBookmaker() throws SQLException {
        final List<Race> races;
        Race race = null;
        Connection conn = null;
        Statement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            ResultSet resultSet = stat.executeQuery("select race.race_id, race.start_time, race.stadium, race.race_date" +
                    " FROM race Inner Join horse_race" +
                    " on ((race.race_id=horse_race.race_id)" +
                    " and (horse_race.coef is null)" +
                    " and (horse_race.result_place is null)" +
                    " and (race.Race_Date>=current_date))" +
                    " group by (race.race_id,race.start_time,race.stadium,race.race_date)");

            races = new ArrayList<Race>();
            while (resultSet.next()) {
                race = new Race();
                race.setRaceId(resultSet.getLong("RACE_ID"));
                race.setRaceDate(resultSet.getDate("RACE_DATE"));
                race.setStadium(resultSet.getString("STADIUM"));
                race.setStartTime(resultSet.getTime("START_TIME"));


                races.add(race);
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return races;
    }

    @Override
    public int addNewHorse(Horse horse) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        int key = 0;
        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("INSERT INTO HORSE" +
                    " (HORSE_NAME) " +
                    "VALUES (?)", Statement.RETURN_GENERATED_KEYS);

            prepStat.setString(1, horse.getHorseName());
            prepStat.executeUpdate();
            ResultSet rs = prepStat.getGeneratedKeys();
            if (rs.next()) {
                // Retrieve the auto generated key(s).
                key = rs.getInt(1);
            }
            return key;

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
    }

    /**
     * Add horse (using for synchronize)
     *
     * @param horse to add
     * @return 1 if everything was alright
     * @throws SQLException if something goes wrong
     */
    private int addHorse(Horse horse) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("INSERT INTO HORSE" +
                    " (HORSE_NAME, HORSE_ID) " +
                    "VALUES (?, ?)");

            prepStat.setString(1, horse.getHorseName());
            prepStat.setLong(2, horse.getHorseId());

            return prepStat.executeUpdate();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
    }

    /**
     * Adds Bet Type (using for synchronize)
     *
     * @param betType to add
     * @return 1 if everything was alright
     * @throws SQLException if something goes wrong
     */
    private int addBetType(BetType betType) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("INSERT INTO BET_TYPE" +
                    " (BET_TYPE, BET_TYPE_ID) " +
                    "VALUES (?, ?)");

            prepStat.setString(1, betType.getBetType());
            prepStat.setLong(2, betType.getBetTypeId());

            return prepStat.executeUpdate();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
    }

    /**
     * Adds information to table horse_race (uses for syncronization)
     *
     * @param horseRace to add
     * @return 1 if everything was alright
     * @throws SQLException if something goes wrong
     */
    private int addHorseRace(HorseRace horseRace) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("INSERT INTO HORSE_RACE" +
                    " (HORSE_ID, RACE_ID, COEF, RESULT_PLACE) " +
                    "VALUES (?, ?, ?, ?)");

            prepStat.setLong(1, horseRace.getHorseId());
            prepStat.setLong(2, horseRace.getRaceId());
            if (horseRace.getCoef() == null) {
                prepStat.setNull(3, Types.DOUBLE);
            } else {
                prepStat.setDouble(3, horseRace.getCoef());
            }
            if (horseRace.getResultPlace() == null) {
                prepStat.setNull(4, Types.INTEGER);
            } else {
                prepStat.setFloat(4, horseRace.getResultPlace());
            }
            //    prepStat.setLong(5, horseRace.getHorseRaceId());

            return prepStat.executeUpdate();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
    }

    /**
     * Creates db's connection pool
     *
     * @return Connection pool
     * @throws SQLException if something goes wrong
     * @throws IOException  if something goes wrong
     */
    protected JdbcConnectionPool createPool() throws SQLException, IOException {
        try {
            Class.forName("org.h2.Driver");
        } catch (ClassNotFoundException e) {
            throw new SQLException(e);
        }
        return JdbcConnectionPool.create(db, "sa", "");
    }

    /**
     * Method wich closes connection in right way
     *
     * @param conn - connection which should be closed
     */
    public void silentClose(Connection conn) {
        System.out.println("destroying connection!");

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                //  ignored or reported to log4j with debug level
            }
        }
    }

    /**
     * Method wich closes statement in right way
     *
     * @param myStat - statement (may be preparedStatement)
     */
    public void silentClose(Statement myStat) {
        if (myStat != null) {
            try {
                myStat.close();
            } catch (SQLException e) {
                //  ignored or reported to log4j with debug level
            }
        }
    }

    public List<HorseRace> findAllHorseRaces() throws SQLException {

        final List<HorseRace> horseRaces;
        HorseRace horseRace = null;
        Connection conn = null;
        Statement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            ResultSet resultSet = stat.executeQuery("SELECT * FROM HORSE_RACE ");

            horseRaces = new ArrayList<HorseRace>();

            while (resultSet.next()) {
                horseRace = new HorseRace();
                horseRace.setHorseRaceId(resultSet.getLong("HORSE_RACE_ID"));
                horseRace.setHorseId(resultSet.getLong("HORSE_ID"));
                horseRace.setResultPlace(resultSet.getInt("RESULT_PLACE"));
                if (resultSet.wasNull()) {
                    horseRace.setResultPlace(null);
                }
                horseRace.setCoef(resultSet.getDouble("COEF"));
                if (resultSet.wasNull()) {
                    horseRace.setCoef(null);
                }
                horseRace.setRaceId(resultSet.getLong("RACE_ID"));


                horseRaces.add(horseRace);
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return horseRaces;
    }

    public List<Client> findAllClients() throws SQLException {
        final List<Client> clients;
        Client client = null;
        Connection conn = null;
        Statement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            ResultSet resultSet = stat.executeQuery("SELECT * FROM CLIENT ");

            clients = new ArrayList<Client>();

            while (resultSet.next()) {
                client = new Client();
                client.setClientId(resultSet.getLong("CLIENT_ID"));
                client.setFirstName(resultSet.getString("FIRST_NAME"));
                client.setLastName(resultSet.getString("LAST_NAME"));
                client.setPassword(resultSet.getLong("PASSWORD"));
                client.setPhoneNumber(resultSet.getString("PHONE_NUMBER"));
                if (resultSet.wasNull()) {
                    client.setPhoneNumber(null);
                }
                client.setEmail(resultSet.getString("EMAIL"));
                client.setMoney(resultSet.getFloat("MONEY"));

                clients.add(client);
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return clients;
    }

    public List<Race> findAllRaces() throws SQLException {
        final List<Race> races;
        Race race = null;
        Connection conn = null;
        Statement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            ResultSet resultSet = stat.executeQuery("SELECT * FROM RACE");

            races = new ArrayList<Race>();

            while (resultSet.next()) {
                race = new Race();
                race.setRaceId(resultSet.getLong("RACE_ID"));
                race.setStadium(resultSet.getString("STADIUM"));
                race.setStartTime(resultSet.getTime("START_TIME"));
                race.setRaceDate(resultSet.getDate("RACE_DATE"));

                races.add(race);
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }
        return races;
    }

    public List<Bet> findAllBets() throws SQLException {
        final List<Bet> bets;
        Bet bet = null;
        Connection conn = null;
        Statement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            ResultSet resultSet = stat.executeQuery("SELECT * FROM BET");

            bets = new ArrayList<Bet>();

            while (resultSet.next()) {
                bet = new Bet();
                bet.setBetId(resultSet.getLong("BET_ID"));
                bet.setClientId(resultSet.getLong("Client_ID"));
                bet.setRaceId(resultSet.getLong("Race_ID"));
                bet.setBetTypeId(resultSet.getLong("BET_TYPE_ID"));
                bet.setFirstHorse(resultSet.getLong("FIRST_HORSE"));
                bet.setSecondHorse(resultSet.getLong("SECOND_HORSE"));
                if (resultSet.wasNull()) {
                    bet.setSecondHorse(null);
                }
                bet.setThirdHorse(resultSet.getLong("THIRD_HORSE"));
                if (resultSet.wasNull()) {
                    bet.setThirdHorse(null);
                }
                bet.setResult(resultSet.getString("RESULT"));
                if (resultSet.wasNull()) {
                    bet.setResult(null);
                }
                bet.setBetDate(resultSet.getDate("BET_DATE"));
                bet.setBetValue(resultSet.getFloat("BET_Value"));
                bets.add(bet);
            }
        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return bets;
    }

    public int synchronizeDbToJson(String jsonDirectory) throws SQLException {
        final List<Race> races;
        final List<Client> clients;
        final List<HorseRace> horseRaces;
        final List<Bet> bets;
        final List<Horse> horses;
        final List<BetType> betTypes;
        int count = 0;
        ObjectMapper mapper = new ObjectMapper();
        File file = null;

        races = findAllRaces();
        count += races.size();

        file = new File(jsonDirectory + "\\races.jsn");
        try {
            mapper.writeValue(file, races);
        } catch (IOException e) {
            throw new SQLException();
        }

        clients = findAllClients();
        count += clients.size();

        file = new File(jsonDirectory + "\\clients.jsn");
        try {
            mapper.writeValue(file, clients);
        } catch (IOException e) {
            throw new SQLException();
        }

        bets = findAllBets();
        count += bets.size();

        file = new File(jsonDirectory + "\\bets.jsn");
        try {
            mapper.writeValue(file, bets);
        } catch (IOException e) {
            throw new SQLException();
        }

        horses = findAllHorses();
        count += horses.size();

        file = new File(jsonDirectory + "\\horses.jsn");
        try {
            mapper.writeValue(file, horses);
        } catch (IOException e) {
            throw new SQLException();
        }

        betTypes = findAllBetTypes();
        count += betTypes.size();

        file = new File(jsonDirectory + "\\betTypes.jsn");
        try {
            mapper.writeValue(file, betTypes);
        } catch (IOException e) {
            throw new SQLException();
        }

        horseRaces = findAllHorseRaces();
        count += horseRaces.size();

        file = new File(jsonDirectory + "\\horseRaces.jsn");
        try {
            mapper.writeValue(file, horseRaces);
        } catch (IOException e) {
            throw new SQLException();
        }

        return count;
    }

    public int executeDdl(File ddlFile, Statement stat) throws IOException, SQLException {

        BufferedReader inputStream = new BufferedReader(new FileReader(ddlFile));
        String sql = null;
        while ((sql = inputStream.readLine()) != null) {
            stat.addBatch(sql.trim());
        }
        stat.executeBatch();
        return 0;
    }
    // todo under construction caused (password in client) - change DDL

    public int synchronizeJsonToDb(String jsonDirectory) throws SQLException {
        final List<Race> races;
        final List<Client> clients;
        final List<HorseRace> horseRaces;
        final List<Bet> bets;
        final List<Horse> horses;
        final List<BetType> betTypes;
        Connection conn = null;
        Statement stat = null;
        File file = null;
        ObjectMapper mapper = new ObjectMapper();

        int count = 0;

        try {
            file = new File(jsonDirectory + "\\races.jsn");
            races = mapper.readValue(file, new TypeReference<ArrayList<Race>>() {
            });
            file = new File(jsonDirectory + "\\clients.jsn");
            clients = mapper.readValue(file, new TypeReference<ArrayList<Client>>() {
            });
            file = new File(jsonDirectory + "\\horseRaces.jsn");
            horseRaces = mapper.readValue(file, new TypeReference<ArrayList<HorseRace>>() {
            });
            file = new File(jsonDirectory + "\\bets.jsn");
            bets = mapper.readValue(file, new TypeReference<ArrayList<Bet>>() {
            });
            file = new File(jsonDirectory + "\\horses.jsn");
            horses = mapper.readValue(file, new TypeReference<ArrayList<Horse>>() {
            });
            file = new File(jsonDirectory + "\\betTypes.jsn");
            betTypes = mapper.readValue(file, new TypeReference<ArrayList<BetType>>() {
            });


        } catch (IOException e) {
            throw new SQLException(e);
        }

        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            //todo CREATE SAVEPOINT
            //todo it is necessary to drop all tables then add create all tables then export data and only
            //todo after that -> create constraints!!!

            executeDdl(
                    new File("DDL/DDL_H2.ddl")
                    , stat);

            for (Race race : races) {
                addRace(race);
            }
            for (Client client : clients) {
                addClient(client);
            }
            for (BetType betType : betTypes) {
                addBetType(betType);
            }
            for (Horse horse : horses) {
                addHorse(horse);
            }
            for (HorseRace horseRace : horseRaces) {
                addHorseRace(horseRace);
            }
            for (Bet bet : bets) {
                addBet(bet);
            }


        } catch (SQLException e) {
            e.printStackTrace();
            // System.out.println("Unable to export data");
        }
        catch (IOException e) {
            System.out.println("Cannot find your file");
        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return 0;
    }

    public Map<Horse, List<Integer>> getHorseHistory() throws SQLException {

        final Map<Horse, List<Integer>> horses;
        Horse horse = null;
        Integer place = null;
        Connection conn = null;
        Statement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            ResultSet resultSet = stat.executeQuery("select horse.horse_id, horse.horse_name, horse_race.result_place " +
                    "from horse Inner join horse_race " +
                    "on horse_race.horse_id=horse.horse_id " +
                    "and horse_race.RESULT_PLACE is not null " +
                    "order by horse.horse_name DESC");

            horses = new HashMap<Horse, List<Integer>>();

            while (resultSet.next()) {
                horse = new Horse();
                horse.setHorseId(resultSet.getLong("HORSE_ID"));
                horse.setHorseName(resultSet.getString("HORSE_NAME"));
                place = resultSet.getInt("RESULT_PLACE");

                if (horses.containsKey(horse)) {
                    horses.get(horse).add(place);
                } else {
                    horses.put(horse, new ArrayList<Integer>());
                    horses.get(horse).add(place);
                }
            }
        } finally {
            silentClose(stat);
            silentClose(conn);
        }
        return horses;
    }

    public List<Horse> getHorsesInRace(Race race) throws SQLException {
        final List<Horse> horses;

        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = getConnection();
            prepStat = conn.prepareStatement("select * from horse inner join horse_race on horse.horse_id=horse_race.horse_ID \n" +
                    "inner join race on horse_race.race_id = race.race_ID AND race.race_id = ?");

            prepStat.setLong(1, race.getRaceId());

            ResultSet resultSet = prepStat.executeQuery();

            horses = new ArrayList<Horse>();
            while (resultSet.next()) {
                horses.add(new Horse(
                        resultSet.getLong("Horse_Id"),
                        resultSet.getString("Horse_name")
                ));
            }

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }

        return horses;
    }

    public Client findClientByEmail(String email) throws SQLException {

        Client client = null;
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("Select * from Client where email = ?");

            prepStat.setString(1, email);
            ResultSet resultSet = prepStat.executeQuery();


            // email is unique and as the result will be one client or null
            if (resultSet.next()) {
                client = new Client();
                client.setClientId(resultSet.getLong("CLIENT_ID"));
                client.setFirstName(resultSet.getString("FIRST_NAME"));
                client.setLastName(resultSet.getString("LAST_NAME"));
                client.setPassword(resultSet.getLong("PASSWORD"));
                client.setPhoneNumber(resultSet.getString("PHONE_NUMBER"));
                if (resultSet.wasNull()) {
                    client.setPhoneNumber(null);
                }
                client.setEmail(resultSet.getString("EMAIL"));
                client.setMoney(resultSet.getFloat("MONEY"));

            }
        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }

        return client;
    }

    public List<Bet> findAllClientBets(Client client) throws SQLException {
        final List<Bet> bets;
        Bet bet = null;
        Connection conn = null;
        PreparedStatement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.prepareStatement("SELECT * FROM BET where Client_id = ?");
            stat.setLong(1, client.getClientId());

            ResultSet resultSet = stat.executeQuery();

            bets = new ArrayList<Bet>();

            while (resultSet.next()) {
                bet = new Bet();
                bet.setBetId(resultSet.getLong("BET_ID"));
                bet.setClientId(resultSet.getLong("Client_ID"));
                bet.setRaceId(resultSet.getLong("Race_ID"));
                bet.setBetTypeId(resultSet.getLong("BET_TYPE_ID"));
                bet.setFirstHorse(resultSet.getLong("FIRST_HORSE"));
                bet.setSecondHorse(resultSet.getLong("SECOND_HORSE"));
                if (resultSet.wasNull()) {
                    bet.setSecondHorse(null);
                }
                bet.setThirdHorse(resultSet.getLong("THIRD_HORSE"));
                if (resultSet.wasNull()) {
                    bet.setThirdHorse(null);
                }
                bet.setResult(resultSet.getString("RESULT"));
                if (resultSet.wasNull()) {
                    bet.setResult(null);
                }
                bet.setBetDate(resultSet.getDate("BET_DATE"));
                bet.setBetValue(resultSet.getFloat("BET_Value"));
                bets.add(bet);
            }
        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return bets;
    }

    public int setCoefs(Map<Race, Map<Horse, Double>> raceCoefs) throws SQLException {
        int[] result = {};
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("UPDATE HORSE_RACE SET COEF = ? WHERE Race_ID = ? AND HORSE_ID = ?");
            for (Race race : raceCoefs.keySet()) {
                for (Horse horse : raceCoefs.get(race).keySet()) {

                    prepStat.setDouble(1, raceCoefs.get(race).get(horse));
                    prepStat.setLong(2, race.getRaceId());
                    prepStat.setLong(3, horse.getHorseId());

                    prepStat.addBatch();
                }
            }

            result = prepStat.executeBatch();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
        return result.length;

    }

    public int setResults(Map<Race, Map<Horse, Integer>> raceResults) throws SQLException {
        int[] result = {};
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("UPDATE HORSE_RACE SET RESULT_PLACE = ? WHERE Race_ID = ? AND HORSE_ID = ?");
            for (Race race : raceResults.keySet()) {
                for (Horse horse : raceResults.get(race).keySet()) {
                    prepStat.setInt(1, raceResults.get(race).get(horse));
                    prepStat.setLong(2, race.getRaceId());
                    prepStat.setLong(3, horse.getHorseId());

                    prepStat.addBatch();
                }
            }

            result = prepStat.executeBatch();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
        return result.length;

    }

    /**
     * @return
     * @throws SQLException
     */
    protected int setBetResult() throws SQLException {

        return 0;
    }


    //todo UNWORKABLE METHOD

    /**
     * CURRENTLY DOESN'T WORK
     *
     * @param races which should be processed
     * @return
     * @throws SQLException
     */
    public int giveRevenueToClients(List<Race> races) throws SQLException {
        //todo TO DO !!!
        int[] result = {};
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("UPDATE HORSE_RACE SET RESULT_PLACE = ? WHERE Race_ID = ? AND HORSE_ID = ?");

//            for (Race race : raceResults.keySet()) {
//                for (Horse horse : raceResults.get(race).keySet()) {
//                    prepStat.setInt(1, raceResults.get(race).get(horse));
//                    prepStat.setLong(2, race.getRaceId());
//                    prepStat.setLong(3, horse.getHorseId());
//
//                    prepStat.addBatch();
//                }
//            }

            result = prepStat.executeBatch();

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
        return result.length;

    }

    public Race findRaceById(long raceId) throws SQLException {
        Race race = null;
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("SELECT * FROM RACE where race_Id = ?");
            prepStat.setLong(1, raceId);
            ResultSet resultSet = prepStat.executeQuery();


            if (resultSet.next()) {
                race = new Race();
                race.setRaceId(resultSet.getLong("RACE_ID"));
                race.setStadium(resultSet.getString("STADIUM"));
                race.setStartTime(resultSet.getTime("START_TIME"));
                race.setRaceDate(resultSet.getDate("RACE_DATE"));

            }

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
        return race;
    }

    public Client findClientById(long clientId) throws SQLException {
        Client client = null;
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("Select * from Client where client_ID = ?");

            prepStat.setLong(1, clientId);
            ResultSet resultSet = prepStat.executeQuery();


            if (resultSet.next()) {
                client = new Client();
                client.setClientId(resultSet.getLong("CLIENT_ID"));
                client.setFirstName(resultSet.getString("FIRST_NAME"));
                client.setLastName(resultSet.getString("LAST_NAME"));
                client.setPassword(resultSet.getLong("PASSWORD"));
                client.setPhoneNumber(resultSet.getString("PHONE_NUMBER"));
                if (resultSet.wasNull()) {
                    client.setPhoneNumber(null);
                }
                client.setEmail(resultSet.getString("EMAIL"));
                client.setMoney(resultSet.getFloat("MONEY"));

            }
        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }

        return client;
    }

    public Horse findHorseById(long horseId) throws SQLException {
        Horse horse = null;
        Connection conn = null;
        PreparedStatement stat = null;

        try {
            conn = getConnection();
            stat = conn.prepareStatement("select * from Horse where horse_id = ?");
            stat.setLong(1, horseId);
            ResultSet resultSet = stat.executeQuery();

            if (resultSet.next()) {
                horse = new Horse();
                horse.setHorseId(resultSet.getLong("Horse_Id"));
                horse.setHorseName(resultSet.getString("Horse_name"));
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return horse;
    }

    public BetType findBetTypeById(long betTypeId) throws SQLException {
        BetType betType = null;
        Connection conn = null;
        PreparedStatement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.prepareStatement("SELECT * FROM BET_TYPE where bet_type_id = ?");
            stat.setLong(1, betTypeId);
            ResultSet resultSet = stat.executeQuery();

            if (resultSet.next()) {
                betType = new BetType();
                betType.setBetTypeId(resultSet.getLong("BET_TYPE_ID"));
                betType.setBetType(resultSet.getString("BET_TYPE"));

            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return betType;
    }

    public Bet findBetById(long betId) throws SQLException {
        Bet bet = null;
        Connection conn = null;
        PreparedStatement stat = null;

        try {
            conn = cp.getConnection();
            stat = conn.prepareStatement("SELECT * FROM BET where bet_id = ?");
            stat.setLong(1, betId);
            ResultSet resultSet = stat.executeQuery();

            if (resultSet.next()) {
                bet = new Bet();
                bet.setBetId(resultSet.getLong("BET_ID"));
                bet.setClientId(resultSet.getLong("Client_ID"));
                bet.setRaceId(resultSet.getLong("Race_ID"));
                bet.setBetTypeId(resultSet.getLong("BET_TYPE_ID"));
                bet.setFirstHorse(resultSet.getLong("FIRST_HORSE"));
                bet.setSecondHorse(resultSet.getLong("SECOND_HORSE"));
                if (resultSet.wasNull()) {
                    bet.setSecondHorse(null);
                }
                bet.setThirdHorse(resultSet.getLong("THIRD_HORSE"));
                if (resultSet.wasNull()) {
                    bet.setThirdHorse(null);
                }
                bet.setResult(resultSet.getString("RESULT"));
                if (resultSet.wasNull()) {
                    bet.setResult(null);
                }
                bet.setBetDate(resultSet.getDate("BET_DATE"));
                bet.setBetValue(resultSet.getFloat("BET_Value"));
            }
        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return bet;
    }

    public Staff findStaffByEmail(String email) throws SQLException {
        Staff staff = null;
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("Select * from Staff where email = ?");

            prepStat.setString(1, email);
            ResultSet resultSet = prepStat.executeQuery();


            // email is unique and as the result will be one client or null
            if (resultSet.next()) {
                staff = new Staff();
                staff.setStaffId(resultSet.getLong("Staff_ID"));
                staff.setFirstName(resultSet.getString("FIRST_NAME"));
                staff.setLastName(resultSet.getString("LAST_NAME"));
                staff.setPassword(resultSet.getLong("PASSWORD"));
                staff.setJob(resultSet.getString("JOB"));
                staff.setEmail(resultSet.getString("EMAIL"));

            }
        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }

        return staff;
    }

    public List<FullRace> getFullRaces(List<Race> races) throws SQLException {

        List<FullRace> fullRaces = null;
        FullRace fullRace = null;

        if (races.size() > 0) {
            fullRaces = new ArrayList<FullRace>();
        }

        for (Race r : races) {
            fullRace = new FullRace();
            fullRace.setRace(r);
            fullRace.setHorses(findAllHorseInRace(r));

            fullRaces.add(fullRace);
        }

        return fullRaces;
    }

    public FullRace getFullRaces(Race race) throws SQLException {
        FullRace fullRace = null;
        fullRace = new FullRace();
        fullRace.setRace(race);
        fullRace.setHorses(findAllHorseInRace(race));
        return fullRace;
    }

    public List<FullBet> getFullBets(List<Bet> bets) throws SQLException {

        List<FullBet> fullBets = null;
        FullBet fullBet = null;

        //  if (bets.size() > 0) {
        fullBets = new ArrayList<FullBet>();
        // }

        for (Bet b : bets) {
            fullBet = new FullBet();
            fullBet.setBet(b);
            fullBet.setClient(findClientById(b.getClientId()));
            fullBet.setRace(findRaceById(b.getRaceId()));
            fullBet.setBetType(findBetTypeById(b.getBetTypeId()));
            fullBet.setFirstHorse(findHorseById(b.getFirstHorse()));
            if (b.getSecondHorse() != null) {
                fullBet.setSecondHorse(findHorseById(b.getSecondHorse()));
            } else {
                fullBet.setSecondHorse(null);
            }
            if (b.getThirdHorse() != null) {
                fullBet.setThirdHorse(findHorseById(b.getThirdHorse()));
            } else {
                fullBet.setThirdHorse(null);
            }

            fullBets.add(fullBet);
        }

        return fullBets;
    }

    public List<Horse> findAllHorseInRace(Race race) throws SQLException {
        final List<Horse> horses;

        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = getConnection();
            prepStat = conn.prepareStatement("Select horse.horse_name, horse.horse_id from Horse " +
                    "Inner Join HORSE_RACE on HORSE.HORSE_ID=horse_race.horse_id and HORSE_RACE.race_Id= ?");

            prepStat.setLong(1, race.getRaceId());

            ResultSet resultSet = prepStat.executeQuery();

            horses = new ArrayList<Horse>();
            while (resultSet.next()) {
                horses.add(new Horse(
                        resultSet.getLong("Horse_Id"),
                        resultSet.getString("Horse_name")
                ));
            }

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }

        return horses;
    }

    public int addNewHorseRace(long raceId, List<Horse> horses) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;


        try {
            conn = cp.getConnection();
            prepStat = conn.prepareStatement("INSERT INTO HORSE_RACE" +
                    " (HORSE_ID, RACE_ID, COEF, RESULT_PLACE) " +
                    "VALUES (?, ?, ?, ?)");

            for (Horse horse : horses) {
                prepStat.setLong(1, horse.getHorseId());
                prepStat.setLong(2, raceId);
                prepStat.setNull(3, Types.DOUBLE);
                prepStat.setNull(4, Types.INTEGER);
                prepStat.addBatch();
            }
            //    prepStat.setLong(5, horseRace.getHorseRaceId());

            return prepStat.executeBatch().length;

        } finally {
            silentClose(prepStat);
            silentClose(conn);
        }
    }

    public Map<Horse, Map<Date, Integer>> horseStatistics(int history) throws SQLException {

        Connection conn = null;
        Statement stat = null;
        Map<Horse, Map<Date, Integer>> results = new HashMap<Horse, Map<Date, Integer>>();
        Map<Date, Integer> resultOfHorse = null;
        Horse horse = null;

        try {
            conn = cp.getConnection();
            stat = conn.createStatement();
            ResultSet resultSet = stat.executeQuery("select horse_id, result_place,race_date from horse_race " +
                    "inner join race " +
                    "on race.race_id = horse_race.race_id " +
                    "where result_place is not null " +
                    "order by race_date desc ");

            while (resultSet.next()) {

                horse = findHorseById(resultSet.getLong("horse_id"));
                if (!results.containsKey(horse)) {
                    resultOfHorse = new HashMap<Date, Integer>();
                    resultOfHorse.put(resultSet.getDate("race_date"), resultSet.getInt("result_place"));
                    results.put(horse, resultOfHorse);
                } else {
                    if (results.get(horse).size() < history) {
                        results.get(horse).put(resultSet.getDate("race_date"), resultSet.getInt("result_place"));
                    }
                }
            }

        } finally {
            silentClose(stat);
            silentClose(conn);
        }

        return results;
    }
}